package com.yidu.web.dao;


import com.yidu.web.model.WsWeather;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class WeatherDao {



    /**
     * 增加
     * @param joke
     * @return
     */
    public int insert(WsWeather joke) {
        Connection con = null;
        PreparedStatement statement = null;

        try {
            con = JDBCTools.getCon();
            String sql = "insert into ws_weather values (null, ?, ?, ?, ?,?,?,?,?)";
            System.out.println("正在执行sql:"+sql);
            statement = con.prepareStatement(sql);
            statement.setString(1, joke.getProvince());
            statement.setString(2, joke.getCity());
            statement.setString(3, joke.getDay0());
            statement.setString(4, joke.getDay1());
            statement.setString(5, joke.getDay2());
            statement.setString(6, joke.getDay3());
            statement.setString(7, joke.getDay4());
            statement.setString(8, joke.getCurTime());

            return statement.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.close(con, statement);
        }
        return 0;
    }

    /**
     * 批量增加
     * @param list
     * @return
     */
    public int insertList(List<WsWeather> list) {
        deleteAll();//先删除次
        Connection con = null;
        PreparedStatement statement = null;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String date = simpleDateFormat.format(new Date());
        try {
            con = JDBCTools.getCon();
            StringBuffer sql = new StringBuffer("insert into ws_weather values ");
            for(WsWeather ws:list){
                sql.append("(null, '"+ws.getProvince()+"', '"+ws.getCity()+"'," +
                        " '"+ws.getDay0()+"','"+ws.getDay1()+"'," +
                        " '"+ws.getDay2()+"','"+ws.getDay3()+"','"+ws.getDay4()+"','"+date+"'),");
            }
            //除去最后一个逗号
            sql.delete(sql.length()-1,sql.length());
            System.out.println("正在执行sql:"+sql.toString());//963852
            statement = con.prepareStatement(sql.toString()) ;
            return statement.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.close(con, statement);
        }
        return 0;
    }

    /**
     * 删除所有
     * @return
     */
    public int deleteAll() {
        Connection con = null;
        PreparedStatement statement = null;

        try {
            con = JDBCTools.getCon();
            String sql = "delete from ws_weather";
            statement = con.prepareStatement(sql);
            return statement.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.close(con, statement);
        }
        return 0;
    }


    /**
     * 查询城市天气
     * @param city
     * @return
     */
    public WsWeather select(String city) {
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = JDBCTools.getCon();
            String sql = "select * from ws_weather where city = ?";
            statement = con.prepareStatement(sql);
            statement.setString(1, city);
            rs = statement.executeQuery();
            while (rs.next()) {
                WsWeather weather = new WsWeather();
                weather.setWid(rs.getInt(1));
                weather.setProvince(rs.getString(2));
                weather.setCity(rs.getString(3));
                weather.setDay0(rs.getString(4));
                weather.setDay1(rs.getString(5));
                weather.setDay2(rs.getString(6));
                weather.setDay3(rs.getString(7));
                weather.setDay4(rs.getString(8));
                weather.setCurTime(rs.getString(9));
                return weather;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.close(con, statement, rs);
        }
        return null;
    }

    /**
     * 根据省份查询城市
     * @param province
     * @return
     */
    public List<WsWeather> selectCity(String province) {
        List<WsWeather> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = JDBCTools.getCon();
            String sql = "select city from ws_weather where province = ?";
            statement = con.prepareStatement(sql);
            statement.setString(1, province);
            rs = statement.executeQuery();
            while (rs.next()) {
                WsWeather weather = new WsWeather();
                weather.setCity(rs.getString(1));

                list.add(weather);

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.close(con, statement, rs);
        }
        return list;
    }
}
